//
//  FMDataSqlManger.m
//  THHuaLv
//
//  Created by TianHe_Wsk on 15/7/6.
//  Copyright (c) 2015年 TH_wsk. All rights reserved.
//

#import "FMDataSqlManger.h"
#import "FMDatabase.h"



@implementation FMDataSqlManger


/**
 *flag 条件 删除表TableName数据
 */
+(BOOL)deleteTable:(NSString*)TableName condition:(NSString *)flag{
    
    if([UtilCheck DataIsNuLL:flag]){
        flag=@"1=1";
    }
    
    FMDatabase *db=[FMDatabase getDBConnection];
    
    if(![db open]){
        [db open];
    }
    NSString *sql=[NSString stringWithFormat:@"delete  FROM %@ where %@ ",TableName,flag];
    BOOL result = [db executeUpdate:sql];
    
    [db close];
    return result;
}



//保存缓存数据
+(BOOL)insertTable:(NSString*)TableName   sqlDate:(NSDictionary*)dict
{
    
    NSDateFormatter * formatter = [[NSDateFormatter  alloc ]  init ];
    [formatter setLocale:[[NSLocale alloc] initWithLocaleIdentifier:[UtilCheck language:@"datePicker.LocalID"]]];
    
    [formatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    
    
    FMDatabase *db=[FMDatabase getDBConnection];
    
    if(![db open]){
        [db open];
    }
    BOOL  result =NO;
    NSArray *keys=[dict allKeys];
    NSString *DBkey=@"";//数据键值
    NSString*DBvalue=@"";//数据值
    
    for(int i=0;i<keys.count;i++){
        DBkey=[NSString stringWithFormat:@"%@%@,",DBkey,[keys objectAtIndex:i]];
        DBvalue=[NSString stringWithFormat:@"%@'%@',",DBvalue,[dict objectForKey:[keys objectAtIndex:i]]];
    }
    NSString *sql=[NSString stringWithFormat:@"insert into %@(%@addtime) values(%@'%@')",TableName,DBkey,DBvalue,[formatter stringFromDate:[NSDate date]]];
    result = [db executeUpdate:sql];
    
    [db close];
    return result;
    
}

/**
 *修改 flag条件 修改表TableName数据
 */
+(BOOL)uploadTable:(NSString *)TableName set:(NSDictionary*)setdict where:(NSDictionary*)dict{
    
    NSDateFormatter * formatter = [[NSDateFormatter  alloc ]  init ];
    [formatter setLocale:[[NSLocale alloc] initWithLocaleIdentifier:[UtilCheck language:@"datePicker.LocalID"]]];
    
    [formatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    
    FMDatabase *db=[FMDatabase getDBConnection];
    
    if(![db open]){
        [db open];
    }
 
    NSString *setValueWhere=@"";//修改条件
    NSString*setValue=@"";//修改值
    for(NSString *dickey in [setdict allKeys]){
        setValue=[NSString stringWithFormat:@"%@%@='%@',",setValue,dickey,[setdict objectForKey:dickey]];
    }
    
    for(NSString *dickey in [dict allKeys]){
        setValueWhere=[NSString stringWithFormat:@"%@%@='%@' and ",setValueWhere,dickey,[dict objectForKey:dickey]];
    }
    
    NSString *sql=[NSString stringWithFormat:@"select *  from %@ where %@ 1=1  LIMIT 1",TableName,setValueWhere];
    
    FMResultSet *fsa= [db executeQuery:sql];
    BOOL isHave=NO;
    while ([fsa next]) {
        isHave=YES;
    }
    if(isHave){
        
        sql=[NSString stringWithFormat:@"update %@ set %@addtime='%@'  where %@ 1=1", TableName ,setValue,[formatter stringFromDate:[NSDate date]],setValueWhere];
        BOOL  result =[db executeUpdate:sql];
        [db close];
        return result;
 
    }
    else{
        [db close];
        return [self insertTable:TableName sqlDate:setdict];
        
    }
    
    
    
    
    
}




+(NSMutableDictionary*)selectTable:(NSString *)TableName   where:(NSDictionary*)dict{
    
    FMDatabase *db=[FMDatabase getDBConnection];
    
    if(![db open]){
        [db open];
    }
    
    NSString *setValueWhere=@"";//修改条件
    
    
    
    for(NSString *dickey in [dict allKeys]){
        
        if(![UtilCheck compareString:dickey ContaincompareString2:@"ORDERBY"]){
            if([UtilCheck  compareString:[dict objectForKey:dickey] ContaincompareString2:@"<>"]){
                setValueWhere=[NSString stringWithFormat:@"%@%@%@ and ",setValueWhere,dickey,[dict objectForKey:dickey]];
            }
            else if([UtilCheck  compareString:[dict objectForKey:dickey] ContaincompareString2:@"Between"]){
                setValueWhere=[NSString stringWithFormat:@"%@%@%@ and ",setValueWhere,dickey,[dict objectForKey:dickey]];
                
            }
            else{
                setValueWhere=[NSString stringWithFormat:@"%@%@='%@' and ",setValueWhere,dickey,[dict objectForKey:dickey]];
            }
        }
    
    }
    
    
    NSString *sql=nil;
            if([[dict allKeys] containsObject:@"ORDERBY"]){
                   sql= [NSString stringWithFormat:@"select *  from %@ where %@ 1=1 ORDER BY %@ LIMIT 1",TableName,setValueWhere,[dict objectForKey:@"ORDERBY"]];
            }
            else{
   sql= [NSString stringWithFormat:@"select *  from %@ where %@ 1=1 LIMIT 1",TableName,setValueWhere];
            }
    
    FMResultSet *fsa= [db executeQuery:sql];
    NSMutableDictionary *result=[[NSMutableDictionary alloc] initWithCapacity:0];
    while ([fsa next]) {
        result=[NSMutableDictionary dictionaryWithDictionary: [fsa resultDictionary]  ];
    }
    [db close];
    
    return result;
}


/**
 * 查找表TableName数据 返回符合条件的limit条数据条数据 limit 0时返回所有
 */
+(NSMutableArray*)selectTableByTableName:(NSString *)TableName   where:(NSDictionary*)wheredict Limit:(NSInteger)limit{
    
 
    
    FMDatabase *db=[FMDatabase getDBConnection];
    
    if(![db open]){
        [db open];
    }
    
    NSString *setValueWhere=@"";//修改条件
    
    
    
    for(NSString *dickey in [wheredict allKeys]){
        
        if(![UtilCheck compareString:dickey ContaincompareString2:@"ORDERBY"]){
            if([UtilCheck  compareString:[wheredict objectForKey:dickey] ContaincompareString2:@"<>"]){
                setValueWhere=[NSString stringWithFormat:@"%@%@%@ and ",setValueWhere,dickey,[wheredict objectForKey:dickey]];
            }
          else if([UtilCheck  compareString:[wheredict objectForKey:dickey] ContaincompareString2:@"Between"]){
                setValueWhere=[NSString stringWithFormat:@"%@%@%@ and ",setValueWhere,dickey,[wheredict objectForKey:dickey]];
              
                        }
            else{
                setValueWhere=[NSString stringWithFormat:@"%@%@='%@' and ",setValueWhere,dickey,[wheredict objectForKey:dickey]];
            }
        }
        
    }
    
    NSString *sql=@"";
    if(limit>0){
        if([[wheredict allKeys] containsObject:@"ORDERBY"]){
              sql=[NSString stringWithFormat:@"select *  from %@ where %@ 1=1  ORDER BY %@ LIMIT %ld",TableName,setValueWhere,[wheredict objectForKey:@"ORDERBY"],(long)limit];
        }
      
        else{
        sql=[NSString stringWithFormat:@"select *  from %@ where %@ 1=1 LIMIT %ld",TableName,setValueWhere,(long)limit];
        }
        
        
    }
    else{
        if([[wheredict allKeys] containsObject:@"ORDERBY"]){
            
                sql=[NSString stringWithFormat:@"select *  from %@ where %@ 1=1  ORDER BY %@",TableName,setValueWhere,[wheredict objectForKey:@"ORDERBY"]];
 
        }
        else{
        sql=[NSString stringWithFormat:@"select *  from %@ where %@ 1=1",TableName,setValueWhere];
        }
        
    }
    
    FMResultSet *fsa= [db executeQuery:sql];
    NSMutableArray *result=[[NSMutableArray alloc] initWithCapacity:0];
    while ([fsa next]) {
        
        [result addObject: [fsa resultDictionary] ];
    }
    [db close];
    return result;
    
    
    
}



@end
